﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IrisGenerator.Common.Modules;
using IrisGenerator.Common.DatabaseModel;
using IrisGenerator.Modules.SqlServer2005.Rules;
using IrisGenerator.Common.DataTypes;

namespace IrisGenerator.Modules.SqlServer2005.Analyzers
{
    [ModuleName("SqlServer 2005 Stored Procedure Analyzer")]
    [ModuleDescription("This module analyzes what basic stored procedures can be written according to a database.")]
    [ModuleVersion(0, 1)]
    [ModuleTarget("SQL2005", "Sql Server 2005", "A module that targets Sql Server 2005 databases.")]
    public class SpAnalyzer:ModuleBase
    {

        [ModuleParameter("Database", "IrisGenerator.Common.DatabaseModel.Database", false, ModuleParameterDirection.InOut)]
        public Database Database { get; set; }

        

        public override bool Analyse()
        {
            SetDatabase();
            
            //For each table in the model, the analyzer generates all procedures needed...
            if (this.Database == null)
                throw new ApplicationException("Database can not be loaded");

            foreach (Table table in this.Database.TableCollection)
            {
                GeneratePrimaryKeyRequest(table);
                GenerateForeignKeyRequests(table);
                GenerateInsertRequest(table);
                GenerateDeleteRequest(table);
                GenerateUpdateRequest(table);
            }

            return true;
        }

        private void GenerateUpdateRequest(Table table)
        {
            if (!SpGenerationRules.CanGenerateUpdate(table))
                return;

            Request request = new Request();
            ConstraintCollection col = table.Constraints.GetConstraintFromConstraintTypeName(ConstraintType.PRIMARY_KEY);
            if (col == null || !col.Count.Equals(1))
                throw new ApplicationException("Error retrieving primary keys");

            ColumnCollection primaryKeyColumns=new ColumnCollection();
            foreach(ConstraintKey key in col[0].Keys)
            {
                primaryKeyColumns.Add(key.PrimaryColumn);
            }

            request.Table = table;
            request.ReqName = table.TabName + "_Update";
            request.Metatype = Metatype.SelectFromCode("GUID");
            request.RequestType = RequestType.Load(new Guid("46A4EF6A-9423-4C3E-8CAA-3A0B2D43D626"));

            request.Insert();

            request.UpdateColumnsCollection.Clear();
            request.RequestParameterCollection.Clear();

            foreach (Column column in table.ColumnCollection)
            {
                if (primaryKeyColumns.Exists(column)==null)
                {
                    UpdateColumns uc = new UpdateColumns();
                    uc.Column = column;
                    uc.Request = request;
                    uc.Insert();
                    request.UpdateColumnsCollection.Add(uc);
                }
                else
                {
                    RequestParameter rp = new RequestParameter();
                    rp.Column = column;
                    rp.Metatype = column.Metatype;
                    rp.ReqParName = column.ColName;
                    rp.Request = request;
                    
                    rp.Insert();
                    request.RequestParameterCollection.Add(rp);
                }
            }

            request.Update();
        }

        private void GenerateDeleteRequest(Table table)
        {
            if (!SpGenerationRules.CanGenerateDelete(table))
                return;

            Request request = new Request();
            ConstraintCollection col = table.Constraints.GetConstraintFromConstraintTypeName(ConstraintType.PRIMARY_KEY);
            if (col == null || !col.Count.Equals(1))
                throw new ApplicationException("Error retrieving primary keys");

            ColumnCollection primaryKeyColumns = new ColumnCollection();
            foreach (ConstraintKey key in col[0].Keys)
            {
                primaryKeyColumns.Add(key.PrimaryColumn);
            }

            request.Table = table;
            request.ReqName = table.TabName + "_Delete";
            request.Metatype = Metatype.SelectFromCode("Integer");
            request.RequestType = RequestType.Load(new Guid("46A4EF6A-9423-4C3E-8CAA-3A0B2D43D626"));

            request.Insert();

            
            request.RequestParameterCollection.Clear();

            foreach (Column column in table.ColumnCollection)
            {
                if (primaryKeyColumns.Exists(column) != null)
                {
                    RequestParameter rp = new RequestParameter();
                    rp.Column = column;
                    rp.Metatype = column.Metatype;
                    rp.ReqParName = column.ColName;
                    rp.Request = request;

                    rp.Insert();
                    request.RequestParameterCollection.Add(rp);
                }
                
            }

            request.Update();
        }

        private void GenerateInsertRequest(Table table)
        {
            if (!SpGenerationRules.CanGenerateInsert(table))
                return;

            Request request = new Request();
            ConstraintCollection col = table.Constraints.GetConstraintFromConstraintTypeName(ConstraintType.PRIMARY_KEY);
            
            ColumnCollection primaryKeyColumns = new ColumnCollection();
            foreach (ConstraintKey key in col[0].Keys)
            {
                primaryKeyColumns.Add(key.PrimaryColumn);
            }

            request.Table = table;
            request.ReqName = table.TabName + "_Insert";
            request.Metatype = Metatype.SelectFromCode("GUID");
            request.RequestType = RequestType.Load(new Guid("46A4EF6A-9423-4C3E-8CAA-3A0B2D43D626"));

            request.Insert();

            request.InsertColumnsCollection.Clear();
            request.RequestParameterCollection.Clear();

            foreach (Column column in table.ColumnCollection)
            {
                InsertColumns ic = new InsertColumns();
                ic.Column = column;
                ic.Request = request;
                ic.Insert();
                request.InsertColumnsCollection.Add(ic);

                if (primaryKeyColumns.Exists(column) != null && primaryKeyColumns.Count.Equals(1) 
                    && primaryKeyColumns[0].Metatype.MetCode.Equals("GUID"))
                {
                    
                }
                else
                {
                    RequestParameter rp = new RequestParameter();
                    rp.Column = column;
                    rp.Metatype = column.Metatype;
                    rp.ReqParName = column.ColName;
                    rp.Request = request;

                    rp.Insert();
                    request.RequestParameterCollection.Add(rp);
                }
            }

            request.Update();
        }

        private void GeneratePrimaryKeyRequest(Table table)
        {
            if (SpGenerationRules.CanGenerateSelectFromPrimaryKey(table))
            {
                Request request = new Request();
                ConstraintCollection col = table.Constraints.GetConstraintFromConstraintTypeName(ConstraintType.PRIMARY_KEY);
                if (col == null || !col.Count.Equals(1))
                    throw new ApplicationException("Error retrieving primary keys");

                request.Table = table;
                request.ReqName = table.TabName + "_SelectOne";
                request.Metatype = Metatype.SelectFromCode("ResultSet");
                request.RequestType = RequestType.Load(new Guid("46A4EF6A-9423-4C3E-8CAA-3A0B2D43D626"));

                request.Insert();
                request.SelectColumnsCollection.Clear();
                request.RequestParameterCollection.Clear();

                foreach (Column column in table.ColumnCollection)
                {
                    SelectColumns sc = new SelectColumns();
                    sc.Column = column;
                    sc.Request = request;
                    sc.Insert();
                    request.SelectColumnsCollection.Add(sc);
                }

                foreach (ConstraintKey key in col[0].Keys)
                {
                    RequestParameter rp = new RequestParameter();
                    rp.Column = key.PrimaryColumn;
                    rp.Metatype = key.PrimaryColumn.Metatype;
                    rp.ReqParName = key.PrimaryColumn.ColName;
                    rp.Request = request;

                    rp.Insert();
                    request.RequestParameterCollection.Add(rp);
                }
                request.Update();
            }
        }

        private void GenerateForeignKeyRequests(Table table)
        {
            if (SpGenerationRules.CanGenerateSelectFromForeignKey(table))
            {

                ConstraintCollection col = table.Constraints.GetConstraintFromConstraintTypeName(ConstraintType.FOREIGN_KEY);
                if (col == null || col.Count.Equals(0))
                    throw new ApplicationException("Error retrieving foreign keys");

                foreach (Constraint constraint in col)
                {
                    Table foreignTable = null;
                    if (constraint.Keys.Count > 0)
                    {
                        foreignTable = constraint.Keys[0].ForeignColumn.Table;
                    }

                    if (foreignTable == null)
                        throw new ApplicationException("Unable to define foreign table");

                    Request request = new Request();

                    request.Table = foreignTable;
                    request.ReqName = constraint.Name;
                    request.Metatype = Metatype.SelectFromCode("ResultSet");
                    request.RequestType = RequestType.Load(new Guid("46A4EF6A-9423-4C3E-8CAA-3A0B2D43D626"));

                    request.Insert();
                    request.SelectColumnsCollection.Clear();
                    request.RequestParameterCollection.Clear();

                   

                    foreach (Column column in foreignTable.ColumnCollection)
                    {
                        SelectColumns sc = new SelectColumns();
                        sc.Column = column;
                        sc.Request = request;
                        sc.Insert();
                        request.SelectColumnsCollection.Add(sc);
                    }

                    foreach (ConstraintKey key in col[0].Keys)
                    {
                        RequestParameter rp = new RequestParameter();
                        rp.Column = key.PrimaryColumn;
                        rp.Metatype = key.PrimaryColumn.Metatype;
                        rp.ReqParName = key.PrimaryColumn.ColName;
                        rp.Request = request;

                        rp.Insert();
                        request.RequestParameterCollection.Add(rp);
                    }
                    request.Update();
                }

            }
        }

        private void SetDatabase()
        {
            if (this.ParametersInstances.Count > 0 &&
                this.ParametersInstances["Database"] != null)
            {
                this.Database = Database.Load(this.ParametersInstances["Database"].ModParInsValue);
                if (this.Database == null)
                {
                    ModuleParameterInstance instance = ModuleParameterInstance.Load(this.ParametersInstances["Database"].ModParInsValue);
                    if (instance == null)
                    {
                        throw new ApplicationException("Unable to load Database parameter");
                    }
                    else
                    {
                        this.Database = Database.Load(instance.ModParInsValue);
                    }
                }
            }
        }

        public override bool Generate()
        {
            return true;
        }

    }
}
